package iedcyt.acute.dataAccess.dao.acute1;

import iedcyt.acute.dataAccess.domain.acute1.CollGroup_Member;
import iedcyt.acute.dataAccess.generic.SpringDao;
import iedcyt.acute.dataAccess.idao.acute1.ICollGroup_MemberDao;

import java.util.ArrayList;
import java.util.List;



public class CollGroup_MemberDao extends SpringDao implements ICollGroup_MemberDao  {

	@Override
	public void create() {
		//comprobamos si la tabla existe y si no la creamos
		try {
			_jdbcTemplate.queryForInt("select count(*) from CollGroup_Member");
		} catch (Exception e) {
			_jdbcTemplate.execute("CREATE TABLE CollGroup_Member (idGr NUMERIC, idAu NUMERIC, percentWithMain NUMERIC, mainResearcher bit)");
			_jdbcTemplate.execute("CREATE INDEX idxCollGroup_Member1 on CollGroup_Member (idGr, idAu) WITH PRIMARY");
			_jdbcTemplate.execute("CREATE INDEX idxCollGroup_Member2 on CollGroup_Member (idGr)");
			_jdbcTemplate.execute("CREATE INDEX idxCollGroup_Member3 on CollGroup_Member (idAu)");
			_jdbcTemplate.execute("ALTER TABLE CollGroup_Member ADD FOREIGN KEY (idAu) REFERENCES Author(idAu)");
			_jdbcTemplate.execute("ALTER TABLE CollGroup_Member ADD FOREIGN KEY (idGr) REFERENCES CollGroup(idGr)");
		}
	}
	
	@Override
	public List<CollGroup_Member> selectAll() {
		
		return _jdbcTemplate.query("SELECT * FROM CollGroup_Member",
				new CollGroup_MemberRowMapper());
	}
	
	@Override
	public void deleteAll() {
		
		_jdbcTemplate.update("DELETE from CollGroup_Member");
	}

	

	@Override
	public void insert(CollGroup_Member doc) {
	
		_jdbcTemplate.update("INSERT INTO CollGroup_Member (idGr, idAu, percentWithMain, mainResearcher) VALUES (?,?,?,?)",
				new Object[] { doc.get_idGr(), doc.get_idAu(), doc.get_percentWithMain(), doc.is_mainResearcher() });
		
	}
	
	@Override
	public void update(CollGroup_Member doc) {
	
		_jdbcTemplate.update("UPDATE CollGroup_Member SET percentWithMain = ?, mainResearcher = ?  WHERE idAu = ? AND idGr = ?",
				new Object[] { doc.get_percentWithMain(), doc.is_mainResearcher(), doc.get_idAu(), doc.get_idGr() });
		
	}

	@Override
	public List<CollGroup_Member> select(int idGr, int idAu) {
		
		return _jdbcTemplate
				.query(
						"SELECT * FROM CollGroup_Member WHERE idGr = ? AND idAu = ?",
						new Object[] { idGr, idAu },
						new CollGroup_MemberRowMapper());
	}

	
	@Override
	public void delete(int idGr, int idAu) {
	
		_jdbcTemplate.update("DELETE from CollGroup_Member WHERE idGr = ? AND idAu = ?",
				new Object[] { idGr, idAu });
		
	}
	
	@Override
	public List<CollGroup_Member> selectByIdAu(int[] idsAu) {
		
		if (idsAu.length != 0)
		{
		String where = "idAu = ? ";
		int cnt = 1;
		for (int i = 0; i < idsAu.length -1 ; i++)
		{
			cnt++;
			where += "OR idAu = ? ";
		}
		
		Object[] objs = new Object[cnt];
		
		for (int i = 0; i < idsAu.length ; i++)
		{
			objs[i] = idsAu[i];
		}
		
		return _jdbcTemplate
				.query(
					"SELECT * FROM CollGroup_Member WHERE " + where,
						objs,
						new CollGroup_MemberRowMapper());
		}
		else return new ArrayList<CollGroup_Member>();
	}

	@Override
	public List<CollGroup_Member> selectByIdGr(int[] idsGr) {
		
		if (idsGr.length !=0)
		{
		String where = "idGr = ? ";
		int cnt = 1;
		for (int i = 0; i < idsGr.length -1 ; i++)
		{
			cnt++;
			where += "OR idGr = ? ";
		}
		
		Object[] objs = new Object[cnt];
		
		for (int i = 0; i < idsGr.length ; i++)
		{
			objs[i] = idsGr[i];
		}
		
		return _jdbcTemplate
				.query(
					"SELECT * FROM CollGroup_Member WHERE " + where,
						objs,
						new CollGroup_MemberRowMapper());
	}
	else return new ArrayList<CollGroup_Member>();
	}

	/**
	 * @see iedcyt.acute.dataAccess.idao.acute1.ICollGroup_MemberDao#selectMainResearcherFromGroup(int)
	 */
	@Override
	public List<CollGroup_Member> selectMainResearcherFromGroup(int idGr) {
		return _jdbcTemplate
		.query(
			"SELECT * FROM CollGroup_Member WHERE idGr = ? AND mainResearcher = 1",
			new Object[] { idGr },
				new CollGroup_MemberRowMapper());

	}
	

}

